package sh.wn36.vehiclemanage.mapper.mapper_cjl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;
import sh.wn36.vehiclemanage.entity.MaintainRecord;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import sh.wn36.vehiclemanage.entity.entity_cjl.Maintain;
import sh.wn36.vehiclemanage.entity.entity_cjl.Violation;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author xk
 * @since 2021-04-26
 */
@Service
public interface MaintainRecordMapper extends BaseMapper<MaintainRecord> {

    @Select("SELECT m.id, c.car_no, d.text, m.main_date, m.main_content, cur.unit_name, m.main_mil, m.main_content, m.next_date, m.next_mil, dri.`name`, m.remarks, m.main_amount FROM maintain_record m LEFT JOIN car c ON m.car_id = c.id LEFT JOIN dictionary d ON m.main_type = d.id LEFT JOIN current_unit cur ON m.main_unit = cur.id LEFT JOIN driver dri ON m.operator = dri.id ${ew.customSqlSegment}")
    IPage<Maintain> listMaintain(Page<Maintain> page, @Param(Constants.WRAPPER)QueryWrapper<Maintain> wrapper);

    @Select("SELECT c.car_no, m.next_date FROM maintain_record m LEFT JOIN car c ON m.car_id = c.id ")
    List<Maintain> listMaintainRecord();

    @Select("SELECT \n" +
            "car.car_no ,\n" +
            "IFNULL(SUM(maintain_record.main_amount),0) AS car_expenses,\n" +
            "\"保养记录表\" AS '数据来源'\n" +
            "FROM \n" +
            "car LEFT JOIN maintain_record ON maintain_record.car_id = car.id \n" +
            "GROUP BY car.`car_no` " +
            "HAVING car_expenses!=0")
    List<Map<String, Object>> reportList();

    @Select("SELECT \n" +
            "car.car_no ,\n" +
            "IFNULL(SUM(maintain_record.main_amount),0) AS car_expenses,\n" +
            "\"保养记录表\" AS '数据来源'\n" +
            "FROM \n" +
            "car LEFT JOIN maintain_record ON maintain_record.car_id = car.id \n" +
            "where car.id=#{carId} "+
            "GROUP BY car.`car_no` " +
            "HAVING car_expenses!=0 ")
    List<Map<String, Object>> queryReportByCarId(Integer carId);
}
